﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Core.Entity;
using System.Data;

namespace Core.Dao
{
    class AccountDao
    {
        public bool insert(Account account)
        {
            object[,] param = {
                {"@CountryId", account.CountryId},
                {"@BankId", account.BankId},
                {"@UserId", account.UserId},
                {"@AccountTypeId", account.AccountTypeId},
                {"@CityId", account.CityId},
                {"@Code", account.Code},
                {"@OwnerName", account.OwnerName},
                {"@DelegateName", account.DelegateName},
                {"@Address", account.Address},
                {"@AlternateEmail", account.AlternateEmail},
                {"@CellPhone", account.CellPhone},
                {"@HomePhone", account.HomePhone},
                {"@FaxNumber", account.FaxNumber},
                {"@TaxCode", account.TaxCode},
                {"@IdNumber", account.IdNumber},
                {"@BirthDay", account.BirthDay},
                {"@HomePage", account.HomePage},
                {"@BankAccountNumber", account.BankAccountNumber},
                {"@IdFrontSide", account.IdFrontSide},
                {"@IdBackSide", account.IdBackSide},
            };
            String sql = "insert into ACCOUNTS(CNT_ID, BNK_ID, USR_ID, ACT_ID, CTY_ID, ACC_CODE, ACC_OWNER_NAME, ACC_DELEGATE_NAME, ACC_ADDRESS, ACC_ALT_EMAIL, ACC_CELL_PHONE, " +
                "ACC_HOME_PHONE, ACC_FAX_NUMBER, ACC_TAX_CODE, ACC_ID_NUMBER, ACC_BIRTHDAY, ACC_HOME_PAGE, ACC_BANK_ACCOUNT_NUMBER, ACC_ID_FRONT_SIDE, ACC_ID_BACK_SIDE) " +
                "values(@CountryId, @BankId, @UserId, @AccountTypeId, @CityId, @Code, @OwnerName, @DelegateName, @Address, @AlternateEmail, @CellPhone, @HomePhone, @FaxNumber, " +
                "@TaxCode, @IdNumber, @BirthDay, @HomePage, @BankAccountNumber, @IdFrontSide, @IdBackSide)";
            return DB.exec(sql, param);
        }

        public bool update(Account account)
        {
            object[,] param = {
                {"@CountryId", account.CountryId},
                {"@BankId", account.BankId},
                {"@UserId", account.UserId},
                {"@AccountTypeId", account.AccountTypeId},
                {"@CityId", account.CityId},
                {"@Code", account.Code},
                {"@OwnerName", account.OwnerName},
                {"@DelegateName", account.DelegateName},
                {"@Address", account.Address},
                {"@AlternateEmail", account.AlternateEmail},
                {"@CellPhone", account.CellPhone},
                {"@HomePhone", account.HomePhone},
                {"@FaxNumber", account.FaxNumber},
                {"@TaxCode", account.TaxCode},
                {"@IdNumber", account.IdNumber},
                {"@BirthDay", account.BirthDay},
                {"@HomePage", account.HomePage},
                {"@BankAccountNumber", account.BankAccountNumber},
                {"@IdFrontSide", account.IdFrontSide},
                {"@IdBackSide", account.IdBackSide},
                {"@id", account.Id},
            };
            String sql = "update ACCOUNTS set CNT_ID = @CountryId, BNK_ID = @BankId, USR_ID = @UserId, ACT_ID = @AccountTypeId, CTY_ID = @CityId, ACC_CODE = @Code, ACC_OWNER_NAME = @OwnerName, " +
                "ACC_DELEGATE_NAME = @DelegateName, ACC_ADDRESS = @Address, ACC_ALT_EMAIL = @AlternateEmail, ACC_CELL_PHONE = @CellPhone, ACC_HOME_PHONE = @HomePhone, ACC_FAX_NUMBER = @FaxNumber, " +
                "ACC_TAX_CODE = @TaxCode, ACC_ID_NUMBER = @IdNumber, ACC_BIRTHDAY = @BirthDay, ACC_HOME_PAGE = @HomePage, ACC_BANK_ACCOUNT_NUMBER = @BankAccountNumber, ACC_ID_FRONT_SIDE = @IdFrontSide, " + 
                "ACC_ID_BACK_SIDE = @IdBackSide where ACC_ID = @id";
            return DB.exec(sql, param);
        }

        public bool delete(int id)
        {
            object[,] param = {
                {"@id", id}   
            };
            String sql = "delete from ACCOUNTS where ACC_ID = @id";
            return DB.exec(sql, param);
        }

        public bool delete(String ids)
        {
            object[,] param = {
                {"@ids", ids}   
            };
            String sql = "delete from ACCOUNTS where ACC_ID in (" + ids + ")";
            return DB.exec(sql);
        }

        public DataTable get(int id)
        {
            object[,] param = {
                {"@id", id}   
            };
            String sql = "select * from ACCOUNTS where ACC_ID = @id";
            return DB.getData(sql, param);
        }

        public DataTable getAll()
        {
            String sql = "select * from ACCOUNTS order by ACC_CODE";
            return DB.getData(sql);
        }

        public DataTable getAccountByUserId(int userId)
        {
            object[,] param = {
                {"@userId", userId}   
            };
            String sql = "select * from ACCOUNTS where USR_ID = @userId";
            return DB.getData(sql, param);
        }
    }
}
